package pe.com.certicom.ittsa.framework.dat;

import java.lang.reflect.InvocationTargetException;
import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.beanutils.PropertyUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
 
import pe.com.certicom.ittsa.framework.exception.DaoException;

public class DaoUtil {
	
	private static final Log log = LogFactory.getLog(DaoUtil.class);
	Connection			conn;
	CallableStatement	cstm;
	ResultSet			rs;
	
	public DaoUtil(){
		conn	=	null;
		cstm	=	null;
		rs		=	null;
	}
	
	protected String nombreStoreProcedure(String procedimiento, int parametros){
		String prepareCall ="";
		prepareCall = "{call "+procedimiento+"(";
		for(int i =0;i<parametros;i++)
		prepareCall = i >= parametros -1 ?	prepareCall + "?": prepareCall+ "?,";
		prepareCall= prepareCall+")}";  
		return prepareCall;
	}
	
	protected void setearVariableEntrada(String sql,Object parametros[]) throws SQLException{
		cstm					= conn.prepareCall(sql);
		int	numeroParametros	= parametros.length;
		for(int i = 1 ; i <= numeroParametros; i++){
			if(parametros[i - 1] == null) 
				cstm.setString(i,"");
			else 
				cstm.setObject(i, parametros[i - 1]);
		}
		log.info(cstm);

	}
	
	public void close(){
        try {
            if(rs != null)
                rs.close();
        }catch(Exception e){
            e.printStackTrace();
        }
        
        try{
            if(cstm != null)
                cstm.close();
        }catch(Exception e){
            e.printStackTrace();
        }
        
        try{
            if(conn != null)
                conn.close();
        }catch(Exception e){
            e.printStackTrace();
        }
    }

	public void closeCstmt(){
	   try{
	      if(cstm != null)cstm.close();
	      }catch(Exception e){
	        e.printStackTrace();
	        }
	    }

	public void commit()throws DaoException{
      try{
            conn.commit();
        }catch(SQLException e){
            throw daoException("::: TRANSACCION ::: ", e.getMessage(), e, null);
        }finally{
            close();
        }
        return;
    }
	 
    @SuppressWarnings("unchecked")
	protected Object obtenerDato(ResultSet rs,Class clase)throws SQLException, 
	InstantiationException, IllegalAccessException, InvocationTargetException, NoSuchMethodException{
	   	int numeroColumnas 		= rs.getMetaData().getColumnCount();
    	String nombreColumnas[]	= new String[numeroColumnas];
    	Object objDato			= null;
    	for(int i =0;i<numeroColumnas;i++){
    		nombreColumnas[i]	=	rs.getMetaData().getColumnName(i+1);
    	 }
        	while(rs.next()){
    		objDato		=	clase.newInstance();
    		
    		for(int i = 0; i< nombreColumnas.length;i++){
    		Object	obj	=	rs.getObject(nombreColumnas[i]);
    		PropertyUtils.setSimpleProperty(objDato, nombreColumnas[i], obj);
    		
    		}
    	}
		return objDato;
	}
	
	@SuppressWarnings("unchecked")
	protected Object dato(String storeProcedure, Class clase,Object parametros[])throws Exception{		
		String sql = nombreStoreProcedure(storeProcedure, parametros.length);
		setearVariableEntrada(sql, parametros);
		cstm.execute();
		rs=(ResultSet)cstm.getResultSet();
		return obtenerDato(rs, clase);
	}
	
	@SuppressWarnings("unchecked")
	protected Object datoSql(String storeProcedure, Class clase,Object parametros[])throws Exception{		
		String sql = nombreStoreProcedure(storeProcedure, parametros.length);
		setearVariableEntrada(sql, parametros);
		cstm.execute();
		rs=(ResultSet)cstm.getResultSet();
		return obtenerDato(rs, clase);
	}
	
	@SuppressWarnings("unchecked")
	protected Object dato(String storeProcedure, Class clase)throws Exception{	
		Object parametros[] = new Object[0];
		return dato(storeProcedure, clase, parametros);
	}
	
	
	@SuppressWarnings("unchecked")
	protected List obtenerDatoLista(ResultSet rs, Class clase)throws SQLException,
	InstantiationException, IllegalAccessException,InvocationTargetException,NoSuchMethodException{
		int			numeroColumnas		=	rs.getMetaData().getColumnCount();
		String 		nombreColumna[]		=	new String[numeroColumnas];
		Object 		objDato				=	null;
		ArrayList	lista				=	new ArrayList();
		for(int i =0;i<numeroColumnas;i++){
			nombreColumna[i] = rs.getMetaData().getColumnName(i+1);
		}
		for(;rs.next(); lista.add(objDato)){
			objDato		= clase.newInstance();
			for(int i = 0;i<nombreColumna.length;i++){
				Object obj	=	rs.getObject(nombreColumna[i]);
				PropertyUtils.setSimpleProperty(objDato, nombreColumna[i], obj);
			}
		}
		return lista;
	}
	
	@SuppressWarnings("unchecked")
	protected List datoLista(String storeProcedure, Class clase, Object parametros[]) throws Exception{
		String sql	= nombreStoreProcedure(storeProcedure, parametros.length);
		setearVariableEntrada(sql, parametros);
		cstm.execute();
		rs			= (ResultSet)cstm.getResultSet();
		return obtenerDatoLista(rs, clase);
	}

    @SuppressWarnings("unchecked")
	protected List datoLista(String storeProcedure, Class clase) throws Exception{
		return datoLista(storeProcedure, clase, new Object[0]);
	}
	
    protected String tipoString (String storeProcedure, Object parametros[])throws Exception{
    	
    	String cadena	= null;
    	String sql		= nombreStoreProcedure(storeProcedure, parametros.length);
    	setearVariableEntrada(sql, parametros);
    	cstm.execute();
    	rs				=	cstm.getResultSet();
    	rs.next();
    	cadena			= 	rs.getString(1);
    	return cadena;
    }
    
    protected BigDecimal tipoBigDecimal(String storeProcedure, Object parametros[]) throws Exception{
    	
    	String sql	= nombreStoreProcedure(storeProcedure, parametros.length+1);
    	setearVariableEntrada(sql, parametros);
    	int numeroParametros	= parametros.length;
    	cstm.registerOutParameter(numeroParametros+1,2);
    	cstm.execute();
    	return cstm.getBigDecimal(numeroParametros+1);
    }
  
    protected String obtenerMensaje(Exception ex){
    	if(ex instanceof SQLException)
            return "::: errors.framework.database.sql:::";
        if(ex instanceof NoSuchMethodException)
            return "::: errors.framework.database.setSimpleProperty:::";
        else
            return "::: errors.framework.database.general:::";    	
    }
 
    protected DaoException daoException(String procedimientoAlmacenado, String mensaje, Exception e, Object parametros[]){
    	e.printStackTrace();
        return new DaoException(obtenerMensaje(e));
    }

}
